/**
 * Copyright &copy; 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
 */
package com.sdkj.dspservice.modules.analysis.dao;

import com.sdkj.dspservice.common.base.dao.BaseDao;
import com.sdkj.dspservice.modules.analysis.entity.PageAnalysis;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * url统计DAO接口
 * @author tm
 */
public interface PageAnalysisDao extends BaseDao<PageAnalysis> {

    /**
     * 获取标签
     * @param pageAnalysis
     * @return
     */
    @Select("select\n" +
            "   concat(domain,attributes['$path'], if(attributes['$query'] != '', '?', ''),attributes['$query']) as url,\n" +
            "   attributes['$title'] as title\n" +
            "from event_all\n" +
            "where event_type = 'page' \n" +
            "  and match(user,'^1[3-9]\\d{9}$') = 1 \n" +
            "  and (toYYYYMMDD(event_time) between #{startDate} and #{endDate})\n" +
            "  and url = #{url} order by toYYYYMMDD(event_time) DESC limit 1"
    )
    PageAnalysis getTitle(PageAnalysis pageAnalysis);

    /**
     * 获取所有url
     */
    @Select("select\n" +
            "    distinct concat(domain,attributes['$path']) as url\n" +
            "from event_all\n" +
            "where toYYYYMMDD(event_time) between #{startDate} and #{endDate}\n" +
            "  and event_type = 'page'\n" +
            "  and match(user,'^1[3-9]\\d{9}$') = 1\n" +
            "  and attributes['$path']  not in ('/hui/portocol/getNoPaperPortocol','/hui/emisSso') and attributes['$path'] not like '%pboss%'\n" +
            " group by url\n" +
            " order by count(distinct user) desc")
    List<String> listUrl(PageAnalysis pageAnalysis);

    /**
     * 获取子节点
     * @param pageAnalysis
     * @return
     */
    @Select("<script> select\n" +
            "    concat(domain,attributes['$path'], if(attributes['$query'] != '', '?', ''),if(attributes['$query'] != '', attributes['$query'], '')) as url,\n" +
            "   anyLast(attributes['$title']) as title\n" +
            "from event_all\n" +
            "where event_type = 'page' \n" +
            "  and match(user,'^1[3-9]\\d{9}$') = 1 \n" +
            "  and (toYYYYMMDD(event_time) between #{startDate} and #{endDate})\n" +
            "  and domain = domain(#{url})\n" +
            "  and attributes['$path'] = path(#{url})\n" +
            "  and match(attributes['$query'], 'y7bRb') = 0" +
            " and length(attributes['$query']) &lt; 160 \n" +
            " and attributes['$path']  not in ('/hui/portocol/getNoPaperPortocol','/hui/emisSso') and attributes['$path'] not like '%pboss%'\n" +
            " and attributes['$query'] not like '%y7%' and attributes['$query'] not like '%token%' \n" +
            " and attributes['$query'] not like '%code%' and  attributes['$query'] not like '%_backurl%'  and attributes['$query'] not like '%yomRbp%' group by url"+
            "</script>"
    )
    List<PageAnalysis> listChildren(PageAnalysis pageAnalysis);

    /**
     * 总汇总
     * @param pageAnalysis
     * @return
     */

    @Select("<script>" +
            "select\n" +
            "    count(distinct user) as uv,\n" +
            "    count(1) as pv,\n" +
            "    '合并' as date,\n" +
            "    '合并' as title\n" +
            "from event_all\n" +
            "where event_type = 'page'\n" +
            "  and match(user,'^1[3-9]\\d{9}$') = 1 \n" +
            "  and (toYYYYMMDD(event_time) between #{startDate} and #{endDate})\n" +
            " and attributes['$path']  not in ('/hui/portocol/getNoPaperPortocol','/hui/emisSso') and attributes['$path'] not like '%pboss%'\n" +
            " and if(attributes['$query'] != '',match(attributes['$query'], 'y7bRb') = 0" +
            " and length(attributes['$query']) &lt; 160 \n" +
            " and attributes['$query'] not like '%y7%' and attributes['$query'] not like '%token%' \n" +
            " and attributes['$query'] not like '%code%' and  attributes['$query'] not like '%_backurl%'  and attributes['$query'] not like '%yomRbp%' ,1=1 ) " +
            "  <if test=\"searchUrlList!=null and searchUrlList.size()>0\">" +
            "   and <if test='isSearchParamMerge!=null and isSearchParamMerge==\"1\"'>(</if> concat(domain,attributes['$path'], if(attributes['$query'] != '', '?', ''),if(attributes['$query'] != '', attributes['$query'], '')) in\n" +
            "   <foreach item=\"searchUrl\" collection=\"searchUrlList\" separator=\",\" open=\"(\" close=\")\"> #{searchUrl} </foreach>" +
            "  </if>" +
            "  <if test='isSearchParamMerge!=null and isSearchParamMerge==\"1\"'>" +
            "   or (concat(domain,attributes['$path'])= concat(domain(#{url}),path(#{url})) and attributes['$query'] != '') <if test='searchUrlList!=null and searchUrlList.size()>0'>)</if>" +
            "  </if>" +
            "</script>")
    List<PageAnalysis> sum(PageAnalysis pageAnalysis);

    /**
     * 按天汇总
     * @param pageAnalysis
     * @return
     */
    @Select("<script>" +
            "select\n" +
            "    toYYYYMMDD(event_time) as date,\n" +
            "    count(distinct user) as uv,\n" +
            "    anyLast(attributes['$title']) as title,\n" +
            "    count(1) as pv\n" +
            "from event_all\n" +
            "where event_type = 'page'\n" +
            "  and match(user,'^1[3-9]\\d{9}$') = 1 \n" +
            "  and (toYYYYMMDD(event_time) between #{startDate} and #{endDate})\n" +
            " and attributes['$path']  not in ('/hui/portocol/getNoPaperPortocol','/hui/emisSso') and attributes['$path'] not like '%pboss%'\n" +
            " and if(attributes['$query'] != '',length(attributes['$query']) &lt;160 \n" +
            " and attributes['$query'] not like '%y7%' and attributes['$query'] not like '%token%' \n" +
            " and attributes['$query'] not like '%code%' and  attributes['$query'] not like '%_backurl%'  and attributes['$query'] not like '%yomRbp%',1=1 ) " +
            "  <if test=\"searchUrlList!=null and searchUrlList.size()>0\">" +
            "   and <if test='isSearchParamMerge!=null and isSearchParamMerge==\"1\"'>(</if> concat(domain,attributes['$path'], if(attributes['$query'] != '', '?', ''),if(attributes['$query'] != '', attributes['$query'], '')) in\n" +
            "   <foreach item=\"searchUrl\" collection=\"searchUrlList\" separator=\",\" open=\"(\" close=\")\"> #{searchUrl} </foreach>" +
            "  </if>" +
            "  <if test='isSearchParamMerge!=null and isSearchParamMerge==\"1\"'>" +
            "   or (concat(domain,attributes['$path'])= concat(domain(#{url}),path(#{url})) and attributes['$query'] != '') <if test='searchUrlList!=null and searchUrlList.size()>0'>)</if>" +
            "  </if>" +
            " group by date order by date" +
            "</script>")
    List<PageAnalysis> sumByDay(PageAnalysis pageAnalysis);

    /**
     * 按地市汇总
     * @param pageAnalysis
     * @return
     */
    @Select("<script>" +
            "select\n" +
            "    if(n.prov_name ='江西', n.city_name, '其他') as city_name,\n" +
            "    count(distinct m.user) as uv,\n" +
            "    sum(m.pv) as pv\n" +
            "from\n" +
            "    (\n" +
            "        select\n" +
            "            concat(domain,attributes['$path'], if(attributes['$query'] != '', '?', ''), if(attributes['$query'] != '', attributes['$query'], '')) as url2,\n" +
            "            user,\n" +
            "            count(1) as pv\n" +
            "        from event_all\n" +
            "        where toYYYYMMDD(event_time) between ${startDate} and ${endDate}\n" +
            "            and event_type = 'page'\n" +
            "            and match(user,'^1[3-9]\\d{9}$') = 1\n" +
            " and attributes['$path']  not in ('/hui/portocol/getNoPaperPortocol','/hui/emisSso') and attributes['$path'] not like '%pboss%'\n" +
            " and if(attributes['$query'] != '',length(attributes['$query']) &lt;160 \n" +
            " and attributes['$query'] not like '%y7%' and attributes['$query'] not like '%token%' \n" +
            " and attributes['$query'] not like '%code%' and  attributes['$query'] not like '%_backurl%'  and attributes['$query'] not like '%yomRbp%',1=1) " +
            "  <if test=\"searchUrlList!=null and searchUrlList.size()>0\">and <if test='isSearchParamMerge!=null and isSearchParamMerge==\"1\"'>(</if>url2 in\n" +
            " <foreach item=\"searchUrl\" collection=\"searchUrlList\" separator=\",\" open=\"(\" close=\")\"> #{searchUrl} </foreach></if>" +
            "  <if test='isSearchParamMerge!=null and isSearchParamMerge==\"1\"'>" +
            "   or (concat(domain,attributes['$path'])= concat(domain(#{url}),path(#{url})) and attributes['$query'] != '') <if test='searchUrlList!=null and searchUrlList.size()>0'>)</if>" +
            "  </if>" +
            "        group by url2,user\n" +
            "    )m\n" +
            " left join (select hlr_code,city_name,prov_name from gio.haoduan_use)n on substring(m.user,1,7) = n.hlr_code\n" +
            " group by city_name" + "</script>")
    List<PageAnalysis> sumByCity(PageAnalysis pageAnalysis);

    /**
     * 按地市和日期汇总
     * @param pageAnalysis
     * @return
     */
    @Select("<script>" +
            "select\n" +
            "    if(n.prov_name ='江西', n.city_name, '其他') as city_name,\n" +
            "    count(distinct m.user) as uv,\n" +
            "    sum(m.pv) as pv,\n" +
            "    m.date as date\n" +
            "from\n" +
            "    (\n" +
            "        select\n" +
            "            concat(domain,attributes['$path'], if(attributes['$query'] != '', '?', ''), if(attributes['$query'] != '', attributes['$query'], '')) as url2,\n" +
            "            user,\n" +
            "            toYYYYMMDD(event_time) date,\n" +
            "            count(1) as pv\n" +
            "        from event_all\n" +
            "        where toYYYYMMDD(event_time) between ${startDate} and ${endDate}\n" +
            "          and event_type = 'page'\n" +
            "          and match(user,'^1[3-9]\\d{9}$') = 1\n" +
            " and attributes['$path']  not in ('/hui/portocol/getNoPaperPortocol','/hui/emisSso') and attributes['$path'] not like '%pboss%'\n" +
            " and if(attributes['$query'] != '',length(attributes['$query']) &lt;160 \n" +
            " and attributes['$query'] not like '%y7%' and attributes['$query'] not like '%token%' \n" +
            " and attributes['$query'] not like '%code%' and  attributes['$query'] not like '%_backurl%'  and attributes['$query'] not like '%yomRbp%',1=1) " +
            "  <if test=\"searchUrlList!=null and searchUrlList.size()>0\">and <if test='isSearchParamMerge!=null and isSearchParamMerge==\"1\"'>(</if>url2 in\n" +
            " <foreach item=\"searchUrl\" collection=\"searchUrlList\" separator=\",\" open=\"(\" close=\")\"> #{searchUrl} </foreach></if>" +
            "  <if test='isSearchParamMerge!=null and isSearchParamMerge==\"1\"'>" +
            "   or (concat(domain,attributes['$path'])= concat(domain(#{url}),path(#{url})) and attributes['$query'] != '') <if test='searchUrlList!=null and searchUrlList.size()>0'>)</if>" +
            "  </if>" +
            "        group by url2,user,date order by date\n" +
            "    )m\n" +
            " left join (select hlr_code,city_name,prov_name from gio.haoduan_use)n on substring(m.user,1,7) = n.hlr_code\n" +
            " group by city_name, date" + "</script>")
    List<PageAnalysis> sumByCityAndDay(PageAnalysis pageAnalysis);
}
